﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace BookStore.Admin.Models
{
    public class CommentModel
    {
        private string connectionString;
        private SqlConnection connect;

        public CommentModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
        }

        public string[][] getAllComment(int page,int limit)
        {
            string[][] result = new string[limit][];
            try
            {
                connect.Open();
                string sqlquery = " WITH LIMIT AS (SELECT C.*, U.user_name AS user_name , B.book_name AS book_name, ROW_NUMBER() OVER ( ORDER BY C.created_date ) AS rowNum FROM Comment C INNER JOIN Users U ON (C.user_id = U.user_id) INNER JOIN Book B ON (B.book_id = C.book_id)) ";
                sqlquery += "SELECT * FROM LIMIT WHERE rowNum BETWEEN " + ((page - 1) * limit + 1) + " AND " + (page * limit);
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                SqlDataReader reader;
                reader = scm.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    result[i] = new string[5];
                    result[i][0] = reader["comment_id"].ToString();
                    result[i][1] = reader["book_name"].ToString();
                    result[i][2] = reader["user_name"].ToString();
                    result[i][3] = reader["description"].ToString();
                    result[i][4] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["created_date"].ToString())).ToString("dd-MM-yyyy");
                    i++;
                }
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
             return result;
        }
        public int countAllComment()
        {
            int result = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT count(*) as total_comment FROM Comment";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_comment"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public int countAllCommentByKeyword(string keyword)
        {
            int result = 0;
            int time = Utils.DateToInt(keyword);
            int time_end = (time + (3600 * 24 - 1));
            int time_start = (time + 1);
            try
            {
                connect.Open();
                string selectSQL = "SELECT count(*) as total_comment FROM Comment WHERE created_date >= " +time_start+" AND created_date <= "+time_end;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_comment"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public string[][] getCommentByKeyword(int page, int limit,string keyword)
        {
            string[][] result = new string[limit][];
            try
            {
                connect.Open();
                int time = Utils.DateToInt(keyword);
                int time_end = (time + (3600 * 24 - 1));
                int time_start = (time + 1);
                string sqlquery = " WITH LIMIT AS (SELECT C.*, U.user_name AS user_name , B.book_name AS book_name, ROW_NUMBER() OVER (ORDER BY C.created_date)AS rowNum FROM Comment C INNER JOIN Users U ON (C.user_id = U.user_id) INNER JOIN Book B ON (B.book_id = C.book_id) WHERE C.created_date <= " + time_end + " AND C.created_date >= " + time_start + " ) ";
                sqlquery += "SELECT * FROM LIMIT WHERE rowNum BETWEEN " + ((page - 1) * limit + 1) + " AND " + (page * limit);
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                SqlDataReader reader;
                reader = scm.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    result[i] = new string[5];
                    result[i][0] = reader["comment_id"].ToString();
                    result[i][1] = reader["book_name"].ToString();
                    result[i][2] = reader["user_name"].ToString();
                    result[i][3] = reader["description"].ToString();
                    result[i][4] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["created_date"].ToString())).ToString("dd-MM-yyyy");
                    i++;
                }
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        public int deleteCommentById(int id)
        {
            try
            {
                connect.Open();
                string sqlquery = "DELETE FROM Comment WHERE comment_id = " + id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                scm.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
            return 0;
        }


     }
}
